"""
DBHelper类
"""
import pymysql
import contextlib


class DBHelper(object):
    # 构造方法__init__
    def __init__(self):
        self.host = '127.0.0.1'
        self.user = 'root'
        self.passwd = '123456'
        self.db = 'employee_db'
        self.charset = 'utf8'

    # 定义上下文管理器，连接后自动关闭连接
    @contextlib.contextmanager
    def cursor(self):
        # 创建数据库连接
        conn = pymysql.connect(host=self.host, port=3306, user=self.user, passwd=self.passwd, db=self.db,
                               charset=self.charset)
        # 以字典的形式获取数据
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            yield cursor
            conn.commit()  # 操作成功提交事务
        except Exception as e:
            print(e)
            conn.rollback()  # 出现异常进行回滚操作
        finally:
            cursor.close()
            conn.close()

    def queryAll(self, cursor, sql, params=None):  # 查询所有的记录
        cursor.execute(sql, params)
        return cursor.fetchall()

    # 执行sql语句，返回影响的记录数
    def execute(self, cursor, sql, params=None):
        effectedNum = cursor.execute(sql, params)
        return effectedNum
